#!/usr/bin/python3
"""
导出zabbix中资源使用第一个版本。
在一个个版本中，所有导出数据的功能都是以方法的形式来编写。
"""
from mytool.zabbix.data_export import database
from mytool.office.excel import SingleExcel
from mytool.system.date import MyDate
import time


class SQL:
    """
    所有sql语句存放。
    """
    def __init__(self):
        now = str(time.time()).split('.')[0]
        four_hour_age = str(int(now) - 3600 * 4)
        print(now)
        print(four_hour_age)
        self.value_table = {
            0: 'trends',        # float
            3: 'trends_uint'    # int
        }
        # defualt all groupid is '[0-9]_'.
        self.all_groupid = """select hg.hostid, g.name as groupname, g.groupid from hosts_groups hg
                            join groups g on hg.groupid = g.groupid
                            where g.name regexp '[0-9]_'"""
        # self.groupid = """select hg.hostid, g.name as groupname, g.groupid from hosts_groups hg
        #                     join groups g on hg.groupid = g.groupid
        #                     where g.name like '67_%'"""
        self.check_host_status = """select status from hosts where hostid = {}"""

        self.host_info = """select h.name, h.host, i.ip from hosts h
        join interface i on h.hostid = i.hostid
        where h.hostid = {}
        """
        self.all_hostid = """select h.hostid, h.host, h.name, i.ip from hosts h
        join interface i on i.hostid = h.hostid
        where status = 0"""
        self.used_mem_itemid = """select itemid, key_, value_type from items 
        where key_ regexp 'vm.memory.size.*(pav|pused)' and hostid = {}"""
        self.total_mem_itemid = """select itemid, key_, value_type from items 
        where key_ regexp 'vm.memory.size.*total' and hostid = {}"""
        self.all_mem_sql = [self.used_mem_itemid, self.total_mem_itemid]

        self.used_disk_itemid = """select itemid, key_ from items 
        where key_ regexp 'vfs.fs.size.*,used' and hostid = {}"""
        self.total_disk_itemid = """select itemid, key_ from items 
        where key_ regexp 'vfs.fs.size.*,total' and hostid = {}"""
        self.all_disk_sql = [self.used_disk_itemid, self.total_disk_itemid]

        self.if_in_itemid = """select itemid, key_ from items 
        where hostid = {} and key_ regexp 'net.if.in.(ens.*|eth.*)' and flags = 4"""
        self.if_out_itemid = """select itemid, key_ from items 
        where hostid = {} and key_ regexp 'net.if.out.(ens.*|eth.*)' and flags = 4"""
        self.if_total_itemid = """select itemid, key_ from items 
        where hostid = {} and key_ regexp 'net.if.total.(ens.*|eth.*)' and flags = 4"""
        self.all_if_sql = [self.if_in_itemid, self.if_out_itemid]

        self.mem_trends = """select clock, value_max from {} where itemid = {} order by clock desc limit 1"""
        self.disk_trends = """select clock, value_max from trends_uint where itemid = {} order by clock desc limit 1"""
        # self.iface_trends = """select clock, value from trends_uint where itemid = {} order by clock desc limit 1"""
        self.iface_trends = """select value_max, clock from trends_uint where itemid = {} order by clock desc limit 1 """


class ExportZabbix:
    """
    获取所有zabbix中的数据。
    validate_time: 数据的校验时间，在4个小时内没有有效数据则此项数据无效设置为-1.
    """
    def __init__(self, file_date, timestamp):
        self.validate_time = round(time.time()) - 3600 * 4
        self.db = database.DB()
        self.sql = SQL()
        self.timestamp = timestamp         # 通过时间戳筛选数据的有效性。
        file_name = 'export_zabbix_{}.xlsx'.format(file_date)
        path = 'f:\\'
        self.excel = SingleExcel(write_path=path + file_name)

    def get_hosts(self):
        hostid_groupname_groupid = self.db.query(self.sql.all_groupid)
        return hostid_groupname_groupid

    def get_host_info(self, hostid):
        _host_info = self.db.query(self.sql.host_info.format(hostid))
        _host_info = _host_info[0]
        if _host_info:
            return [_host_info['name'], _host_info['ip']]
        else:
            assert ValueError

    def get_mem(self, hostid):
        mem = [0, 0]
        for sql in self.sql.all_mem_sql:
            itemid = self.db.query(sql.format(hostid))
            if not itemid:
                continue
            itemid = itemid[0] if itemid else itemid
            table_name = self.sql.value_table[itemid['value_type']]
            _mem = self.db.query(self.sql.mem_trends.format(table_name, itemid['itemid']))
            if not _mem:
                continue
            if _mem[0]['clock'] < self.timestamp:
                continue
            key_ = itemid['key_']
            # mem.append(key_)      # 数据库中key_字段值。
            if 'available' in key_:
                percent = round(100 - _mem[0]['value_max'], 2)
                mem[0] = str(percent) + '%'
            elif 'used' in key_:
                percent = round(_mem[0]['value_max'], 2)
                mem[0] = str(percent) + '%'
            elif 'total' in key_:
                _mem = _mem[0]['value_max']
                _mem = _mem / 1024 / 1024 / 1024
                mem[1] = str(self.closest(_mem))
        return mem

    def get_disk(self, hostid):
        """
        获取磁盘已使用和分配总量。
        :param hostid:
        :return:
        """
        total = []
        for sql in self.sql.all_disk_sql:
            itemids = self.db.query(sql.format(hostid))
            if not itemids:
                continue

            _sum = 0
            for itemid in itemids:
                key_ = itemid['key_']
                if len(key_.split('/')) > 2:    # 盘符超过2个以上的/就不是我们需要的监控项，直接跳过。
                    continue
                disk = self.db.query(self.sql.disk_trends.format(itemid['itemid']))
                if not disk:
                    continue
                disk = disk[0] if disk else disk
                # if disk and disk['clock'] > self.timestamp:
                if disk['clock'] > self.timestamp:
                    _sum += int(disk['value_max'])
            _sum = round(_sum / 1024 / 1024 / 1024, 2)
            total.append(_sum)
        if total[0] == 0 or total[1] == 0:
            total.append(0)
        else:
            try:
                total.append(str(round(total[0] / total[1] * 100, 2)) + '%')
            except:
                print(hostid, total)
                exit()
        return total

    def get_iface(self, hostid):
        iface_data = [0, 0]
        for n, sql in enumerate(self.sql.all_if_sql):
            itemid = self.db.query(sql.format(hostid))
            if not itemid:
                iface_data[n] = '无监控项'
                continue
            _iface = self.db.query(self.sql.iface_trends.format(itemid[0]['itemid']))
            if not _iface:
                continue
            if not _iface[0]['value_max']:
                iface_data[n] = '无数据'
                continue
            if _iface[0]['clock'] < self.validate_time:
                iface_data[n] = '超出时间范围'
                continue
            iface_data[n] = _iface[0]['value_max']
        return iface_data

    def closest(self, mem):
        mem_value = [2, 4, 6, 8, 10, 12, 16, 24, 32, 64, 128, mem]
        mem_value = sorted(mem_value)
        i = mem_value.index(mem)
        if i + 1 == len(mem_value):
            return mem_value[i]
        if abs(mem_value[i - 1] - mem) > abs(mem_value[i + 1] - mem):
            return mem_value[i + 1]
        else:
            return mem_value[i - 1]

    def check_status(self, hostid):
        """
        检查主机是启用还是禁用。
        """
        status = self.db.query(self.sql.check_host_status.format(hostid))
        if status[0]['status'] == 0:
            return True
        else:
            return False

    def main(self):
        enable_host = []
        disable_host = []
        hosts = self.get_hosts()
        enable_host.append(['业务单位', '虚拟机名', 'IP地址', 'CPU使用率', '内存使用率', '分配内存', '已使用磁盘', '分配磁盘', '网卡接收流量', '网卡发送流量'])
        for n, hostid in enumerate(hosts):
            print('total progressing: ' + str(round(n / len(hosts) * 100)) + '%', n, '/', len(hosts))
            _data = []
            _hostid = hostid['hostid']
            _data.append(hostid['groupname'])

            for info in self.get_host_info(_hostid):
                _data.append(info)
            if not self.check_status(_hostid):
                disable_host.append(_data)
                continue
            for mem in self.get_mem(_hostid):
                _data.append(mem)
            for disk in self.get_disk(_hostid):
                _data.append(disk)
            for iface in self.get_iface(_hostid):
                _data.append(iface)
            enable_host.append(_data)

        self.excel.add_sheet('监控中的主机')
        self.excel.write_all_sheet(enable_host, '监控中的主机')
        self.excel.add_sheet('禁用主机')
        self.excel.write_all_sheet(disable_host, '禁用主机')


if __name__ == '__main__':
    mydate = MyDate()
    file_date = str(mydate.month()) + '-' + str(mydate.day())
    start = time.time()
    zbx = ExportZabbix(file_date, mydate - '4h')
    zbx.main()
    print(round((time.time() - start) / 60, 2), 'min')

